package br.com.bancodecurriculos.controleacesso.dao.impl;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import br.com.bancodecurriculos.controleacesso.dao.IPerfilDAOHelper;
import br.com.bancodecurriculos.controleacesso.dao.IUsuarioDAO;
import br.com.bancodecurriculos.controleacesso.dominio.Perfil;
import br.com.bancodecurriculos.controleacesso.dominio.Usuario;
import br.com.bancodecurriculos.util.dao.JDBCConnectionFactory;
import br.com.bancodecurriculos.util.exception.RegistroExistenteException;
import br.com.bancodecurriculos.util.exception.RegistroInexistenteException;

public class UsuarioDAO implements IUsuarioDAO {

	/*
	 * private static final String SQL_INSERT = "usuario.insert"; private static
	 * final String SQL_UPDATE = "usuario.update"; private static final String
	 * SQL_SELECT = "usuario.select"; private static final String SQL_DELETE =
	 * "usuario.delete"; private static final String SQL_SELECT_BY_LOGIN_SENHA =
	 * "usuario.getByLoginSenha";
	 * 
	 * private static final String USUARIO_EXISTENTE_EXCEPTION =
	 * "usuario.exception.usuario_existente"; private static final String
	 * USUARIO_INEXISTENTE_EXCEPTION = "usuario.exception.usuario_inexistente";
	 */

	private static final String SQL_INSERT = "INSERT INTO usuario (login, senha, id_perfil) VALUES (?, ?, ?)";
	private static final String SQL_UPDATE = "UPDATE usuario SET login = ?, senha = ?, id_perfil = ? WHERE id = ?";
	private static final String SQL_SELECT = "SELECT * FROM usuario WHERE id = ?";
	private static final String SQL_DELETE = "DELETE FROM usuario WHERE id = ?";
	private static final String SQL_SELECT_BY_LOGIN_SENHA = "SELECT usuario.id AS id, usuario.login AS login, usuario.senha AS senha,"
			+ " perfil.id AS id_perfil, perfil.nome AS nome_perfil FROM usuario INNER JOIN perfil ON usuario.id_perfil = perfil.id"
			+ " WHERE usuario.login = ? AND usuario.senha = ?";

	private static final String USUARIO_EXISTENTE_EXCEPTION = "Usu�rio j� existe!";
	private static final String USUARIO_INEXISTENTE_EXCEPTION = "Usu�rio inexistente!";

	private IPerfilDAOHelper perfilDAOHelper;

	public UsuarioDAO() {
		this.perfilDAOHelper = new PerfilDAOHelper();
	}

	@Override
	public void save(Usuario t) throws ClassNotFoundException, SQLException,
			RegistroExistenteException, IOException {
		if (this.getByLoginSenha(t.getLogin(), t.getSenha()) == null) {
			PreparedStatement preparedStatement = JDBCConnectionFactory
					.getInstance().getConnection().prepareStatement(SQL_INSERT);
			preparedStatement.setString(1, t.getLogin());
			preparedStatement.setString(2, t.getSenha());
			preparedStatement.setLong(3, t.getPerfil().getId());
			preparedStatement.executeUpdate();
			preparedStatement.close();
		} else {
			throw new RegistroExistenteException(USUARIO_EXISTENTE_EXCEPTION);
		}
	}

	@Override
	public void update(Usuario t) throws ClassNotFoundException, SQLException,
			RegistroInexistenteException, IOException {
		if (this.getByLoginSenha(t.getLogin(), t.getSenha()) != null) {
			PreparedStatement preparedStatement = JDBCConnectionFactory
					.getInstance().getConnection().prepareStatement(SQL_UPDATE);
			preparedStatement.setString(1, t.getLogin());
			preparedStatement.setString(2, t.getSenha());
			preparedStatement.setLong(3, t.getPerfil().getId());
			preparedStatement.setLong(4, t.getId());
			preparedStatement.executeUpdate();
			preparedStatement.close();
		} else {
			throw new RegistroInexistenteException(
					USUARIO_INEXISTENTE_EXCEPTION);
		}
	}

	@Override
	public Usuario getById(Long id) throws ClassNotFoundException,
			SQLException, IOException {
		Usuario usuario = null;
		PreparedStatement preparedStatement = JDBCConnectionFactory
				.getInstance().getConnection().prepareStatement(SQL_SELECT);
		preparedStatement.setLong(1, id);
		ResultSet resultSet = preparedStatement.executeQuery();
		if (resultSet.next()) {
			Perfil perfil = this.perfilDAOHelper.getById(resultSet
					.getLong("id_perfil"));
			usuario = new Usuario(resultSet.getString("login"),
					resultSet.getString("senha"), perfil);
			usuario.setId(resultSet.getLong("id"));
		}
		return usuario;
	}

	@Override
	public void delete(Long id) throws ClassNotFoundException, SQLException,
			RegistroInexistenteException, IOException {
		if (this.getById(id) != null) {
			PreparedStatement preparedStatement = JDBCConnectionFactory
					.getInstance().getConnection().prepareStatement(SQL_DELETE);
			preparedStatement.setLong(1, id);
			preparedStatement.executeUpdate();
			preparedStatement.close();
		} else {
			throw new RegistroInexistenteException(
					USUARIO_INEXISTENTE_EXCEPTION);
		}
	}

	@Override
	public Usuario getByLoginSenha(String login, String senha)
			throws ClassNotFoundException, SQLException, IOException {
		Usuario usuario = null;
		PreparedStatement preparedStatement = JDBCConnectionFactory
				.getInstance().getConnection()
				.prepareStatement(SQL_SELECT_BY_LOGIN_SENHA);
		preparedStatement.setString(1, login);
		preparedStatement.setString(2, senha);
		ResultSet resultSet = preparedStatement.executeQuery();
		if (resultSet.next()) {
			Perfil perfil = new Perfil(resultSet.getString("nome_perfil"));
			perfil.setId(resultSet.getLong("id_perfil"));
			usuario = new Usuario(resultSet.getString("login"),
					resultSet.getString("senha"), perfil);
			usuario.setId(resultSet.getLong("id"));
		}
		return usuario;
	}

	@Override
	public IPerfilDAOHelper getPerfilDAOHelper() {
		return this.perfilDAOHelper;
	}

}
